package rdbms;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DAO.DatabasePool;


import model.FTConceptTime;

public class RDBMSFTConceptTimeDAO {

	private String idConnection;
	private final String name = "CONCEPT_TIME";

	private static String INSERT = "insert into FT_CONCEPT_TIME (CONCEPT_ID, TIME_ID, FREQUENCY) values (?, ?, ?)";

	private static String DELETE = "delete from FT_CONCEPT_TIME where CONCEPT_ID = ? and TIME_ID = ?";

	private static String DELETE_ALL = "delete from FT_CONCEPT_TIME";
	
	private static String SELECT_BY_ID = "select *"
		+ "from FT_CONCEPT_TIME where CONCEPT_ID = ? and TIME_ID = ?";
	
	private static String SELECT_BY_FREQUENCE_MORE = "select CONCEPT_ID, TIME_ID "
			+ "from FT_CONCEPT_TIME where FREQUENCY > ? ";

	private static String SELECT_BY_FREQUENCE_MINOR = "select CONCEPT_ID, TIME_ID "
			+ "from FT_CONCEPT_TIME where FREQUENCY < ? ";

	private static String SELECT_ALL = "select * from FT_CONCEPT_TIME";

	private static String UPDATE_FREQUENCY = "update FT_CONCEPT_TIME set FREQUENCY = FREQUENCY + ? where CONCEPT_ID = ? and TIME_ID = ?";

	public RDBMSFTConceptTimeDAO(final String idConnection) throws SQLException {
		this.idConnection = idConnection;
	}

	public boolean insert(FTConceptTime ftConceptTime) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(INSERT);
			pst.setInt(1, ftConceptTime.getConceptId());
			pst.setInt(2, ftConceptTime.getTimeId());
			pst.setLong(3, ftConceptTime.getFrequency());
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while inserting " + name, e);
		} finally {
			if (success) {
				//conn.commit();
				System.out.println(name + " Insert - Commit");
			} else {
				//conn.rollback();
				System.out.println(name + " Insert - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public boolean delete(int id) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(DELETE);
			pst.setInt(1, id);
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while deleting " + name, e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println(name + " - Delete - Commit");
			} else {
				conn.rollback();
				System.out.println(name + " - Delete - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public boolean deleteAll() throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(DELETE_ALL);
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while deleting(all) " + name, e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println(name + " - Delete(all) - Commit");
			} else {
				conn.rollback();
				System.out.println(name + " - Delete(all) - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public FTConceptTime find(int concept_id, int relation_id, int time_id) throws Exception {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		FTConceptTime ftConceptTime = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(SELECT_BY_ID);
			pst.setInt(1, concept_id);
			pst.setInt(2, time_id);
			rss = pst.executeQuery();
			if (rss.next()) {
				ftConceptTime = new FTConceptTime(rss.getInt(1), rss.getInt(2),
						rss.getInt(3), rss.getInt(4));
			}
			return ftConceptTime;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving " + name, e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}

	public boolean update(FTConceptTime ftConceptTime, int qtd) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(UPDATE_FREQUENCY);
			pst.setInt(1, qtd);
			pst.setInt(2, ftConceptTime.getConceptId());
			pst.setInt(3, ftConceptTime.getTimeId());
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while updating " + name, e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println(name + " - Update - Commit");
			} else {
				conn.rollback();
				System.out.println(name + " - Update - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public List<FTConceptTime> list() throws Exception {
		List<FTConceptTime> list = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		//int ctr = 0;
		try {
			conn = DatabasePool.getConnection(idConnection);
			list = new ArrayList<FTConceptTime>();
			pst = conn.prepareStatement(SELECT_ALL);
			rss = pst.executeQuery();
			while (rss.next()) {
				FTConceptTime ftConceptTime = new FTConceptTime(rss.getInt(1), rss.getInt(2),
						rss.getInt(3), rss.getInt(4));
				list.add(ftConceptTime);
			}
			return list;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving " + name, e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}
}
